package com.smartwmx.daomain.impl;

import com.smartwmx.dao.DBoperator;
import com.smartwmx.daomain.entity.guanli_user;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Repository
public class guanli_userDaompl implements guanli_userDao {


    @Override
    public void update(guanli_user guanli_user) {
        String sql = "update  guanli_user set guanli_id=?,guanli_name=?,guanli_username=?,guanli_userpwd=?,guanli_phonenum=?"
                +"where guanli_id=?";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        db = new DBoperator();
        try {
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1, guanli_user.getGuanli_id());
            pstmt.setString(2, guanli_user.getGuanli_name());
            pstmt.setString(3, guanli_user.getGuanli_username());
            pstmt.setString(4, guanli_user.getGuanli_userpwd());
            pstmt.setString(5, guanli_user.getGuanli_phonenum());
            pstmt.setString(6, guanli_user.getGuanli_id());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
    }

    @Override
    public void delete(String guanli_id) {
        String sql="delete from guanli_user where guanli_id=?";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db= new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setInt(1, Integer.parseInt(guanli_id));
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
    }

    @Override
    public void insert(guanli_user guanli_user) {
        String sql = "insert into guanli_user(guanli_id,guanli_name,guanli_username,guanli_userpwd,guanli_phonenum) VALUES(?,?,?,?,?)";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db = new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1, guanli_user.getGuanli_id());
            pstmt.setString(2, guanli_user.getGuanli_name());
            pstmt.setString(3, guanli_user.getGuanli_username());
            pstmt.setString(4, guanli_user.getGuanli_userpwd());
            pstmt.setString(5, guanli_user.getGuanli_phonenum());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }

    }

    @Override
    public guanli_user queryById(String guanli_id) {
        String sql = "select guanli_name,guanli_username,guanli_userpwd,guanli_phonenum" +
                " from guanli_user where guanli_id=?";
        PreparedStatement pstmt = null;
        DBoperator db = null;
        try {
            db = new DBoperator();
            pstmt = db.getConnection().prepareStatement(sql);
            pstmt.setString(1, guanli_id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String names = rs.getString(1);
                String usernames = rs.getString(2);
                String pwd = rs.getString(3);
                String num = rs.getString(4);
                guanli_user stuser = new guanli_user();
                stuser.setGuanli_id(guanli_id);
                stuser.setGuanli_name(names);
                stuser.setGuanli_username(usernames);
                stuser.setGuanli_userpwd(pwd);
                stuser.setGuanli_phonenum(num);
                return stuser;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }
        return null;
    }


    @Override
    public List queryALL() {
        String sql="select * from guanli_user";
        Statement stmt = null;
        DBoperator db = null;
        List list= null;
        try {
            db = new DBoperator();
            stmt = db.getConnection().createStatement();
            ResultSet rs=stmt.executeQuery(sql);
            list = new ArrayList();
            while(rs.next()){
                String guanli_id=rs.getString(1);
                String guanli_name=rs.getString(2);
                String guanli_username=rs.getString(3);
                String guanli_userpwd=rs.getString(4);
                String guanli_phonenum=rs.getString(5);
                guanli_user stuser= new guanli_user();
                stuser.setGuanli_id(guanli_id);
                stuser.setGuanli_name(guanli_name);
                stuser.setGuanli_username(guanli_username);
                stuser.setGuanli_userpwd(guanli_userpwd);
                stuser.setGuanli_phonenum(guanli_phonenum);
                list.add(stuser);

            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.close();
        }

        return null;
    }
}

